﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml;
using DataAchive.Helper;
using DataAchive.Model.ScreenModel;
using DataAchive.Model.ViewModels;
using MySql.Data.MySqlClient;
using Newtonsoft.Json;

namespace DataAchive.web
{
   public partial class StatisticsWeb
    {
        private M_QueryBuilder queryBuilder = new M_QueryBuilder();

        private static List<string> elClassfiPriperty=new List<string>();
        private static List<string> viClassfiPriperty=new List<string>();

        public static void GetXmlSetting()
        {
            if(elClassfiPriperty.Count()==0|| viClassfiPriperty.Count() == 0)
            {
                XmlNode elClassIfyPriority = Setting.GetXmlNode("setting/ClassIfy/ClassIfyPriority");
                if (elClassIfyPriority != null)
                {
                    elClassfiPriperty.Clear();
                    for (int i = 0; i < elClassIfyPriority.ChildNodes.Count; i++)
                    {
                        elClassfiPriperty.Add(elClassIfyPriority.ChildNodes[i].InnerText);
                    }

                }
                XmlNode viClassIfyPriority = Setting.GetXmlNode("setting/ViClassIfy/ClassIfyPriority");
                if (viClassIfyPriority != null)
                {
                    viClassfiPriperty.Clear();
                    for (int i = 0; i < viClassIfyPriority.ChildNodes.Count; i++)
                    {
                        viClassfiPriperty.Add(viClassIfyPriority.ChildNodes[i].InnerText);
                    }

                }
            }


        }

        
        /// <summary>
        /// 获取最新的检测
        /// </summary>
        /// <returns></returns>
        public  List<DetectDetail> GetTop6DetectDetail()
        {
            List<DetectDetail> detectDetails = new List<DetectDetail>();          
            string sql = @"select  info.classify as faultName,count(*)as FaultNumer, lotid,date from ng inner join info on info.ngId=ng.id
group by classify,lotid order by date desc limit 6";

            List<MySqlParameter> parameters = new List<MySqlParameter>();
  
            var res = queryBuilder.QuerySql<DetectDetail>(sql, parameters.ToArray());
            detectDetails.AddRange(res);
            return detectDetails;
        }

        private List<int> GetDetectElAndVi()
        {
            List<int> nums = new List<int>();
            
            string sql = @"select  count(*) from ng inner join info on info.ngId=ng.id
where type=0 union
select  count(*) from ng inner join info on info.ngId=ng.id
where type=1";
            List<MySqlParameter> parameters = new List<MySqlParameter>();
           
            var res = queryBuilder.QuerySql<int>(sql, parameters.ToArray());
            nums.AddRange(res);
            return nums;
        }

        /// <summary>
        /// 获取统计信息源数据el
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        private List<StatisticsClassifyModel> GetStatisticsSourceELAlltime()
        {         
            string sql = $@"    select lotid,classify,ng.date ,count(*)as Num,GROUP_CONCAT(ngid,',')as ids from ng left join info on ng.id =info.ngId 
   where lotid not in(
     select lotid from ng left join info on ng.id =info.ngId where info.classify is null   
  and lotid not in(select  T.lotid from
    (select info.classify ,ng.lotid,count(*)as count from ng left join info on ng.id =info.ngId 
     group by info.classify ,ng.lotid )T group by  T.lotid having count(*)>1 )
  )
  and info.classify is not null 
   group by ng.lotid, info.classify
                                ";

            List<MySqlParameter> parameters = new List<MySqlParameter>();
          
            List<StatisticsClassifyModel> list = new List<StatisticsClassifyModel>();

          
            list.AddRange(queryBuilder.QuerySql<StatisticsClassifyModel>(sql, parameters.ToArray()));

            string sqlOk = $@"      select max(ng.id)as id, lotid,ng.date ,'OK' as classify,count(distinct(lotid))as Num,GROUP_CONCAT(ng.lotid,',')as ids from ng left join info on ng.id =info.ngId where info.classify is null    
  and lotid not in(select  T.lotid from
    (select info.classify ,ng.lotid,count(*)as count from ng left join info on ng.id =info.ngId 
     group by info.classify ,ng.lotid )T group by  T.lotid having count(*)>1 ) ;
                                ";
            list.AddRange(queryBuilder.QuerySql<StatisticsClassifyModel>(sqlOk, parameters.ToArray()));

            string sqlNg = $@"    select max(T.id)as id, lotid,null as date,'NG' as classify,count(*)as Num,GROUP_CONCAT(t.id,',')as ids from (
   select max(info.id)as id, lotid,classify,max(ng.date)as date,count(*)as Num,max(ng.id)as ngid,GROUP_CONCAT(ngid,',')as ids from ng left join info on ng.id =info.ngId 
   where lotid not in(
     select lotid from ng left join info on ng.id =info.ngId where info.classify is null 
  and lotid not in(select  T.lotid from
    (select info.classify ,ng.lotid,count(*)as count from ng left join info on ng.id =info.ngId 
     group by info.classify ,ng.lotid )T group by  T.lotid having count(*)>1 )
  )
  and info.classify is not null 
   group by ng.lotid
   )T";
            list.AddRange(queryBuilder.QuerySql<StatisticsClassifyModel>(sqlNg, parameters.ToArray()));
            return list;
        }


        /// <summary>
        /// 获取统计信息源数据 VI
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        private List<StatisticsClassifyModel> GetStatisticsSourceVIAlltime()
        {
            
            string sql = $@"    select lotid,classify,ng.date ,count(*)as Num,GROUP_CONCAT(ngid,',')as ids from ng left join info on ng.id =info.ngId 
   where lotid not in(
     select lotid from ng left join info on ng.id =info.ngId where info.classify is null  
  and lotid not in(select  T.lotid from
    (select info.classify ,ng.lotid,count(*)as count from ng left join info on ng.id =info.ngId 
     group by info.classify ,ng.lotid )T group by  T.lotid having count(*)>1 )
  )
  and info.classify is not null 
   group by ng.lotid, info.classify
                                ";

            List<MySqlParameter> parameters = new List<MySqlParameter>();

            List<StatisticsClassifyModel> list = new List<StatisticsClassifyModel>();

            list.AddRange(queryBuilder.QuerySql<StatisticsClassifyModel>(sql, parameters.ToArray()));

            string sqlOk = $@"      select max(ng.id)as id, lotid,ng.date ,'OK' as classify,count(distinct(lotid))as Num,GROUP_CONCAT(ng.lotid,',')as ids from ng left join info on ng.id =info.ngId where info.classify is null 
  and lotid not in(select  T.lotid from
    (select info.classify ,ng.lotid,count(*)as count from ng left join info on ng.id =info.ngId 
     group by info.classify ,ng.lotid )T group by  T.lotid having count(*)>1 ) ;
                                ";
            list.AddRange(queryBuilder.QuerySql<StatisticsClassifyModel>(sqlOk, parameters.ToArray()));

            string sqlNg = $@"    select max(T.id)as id, lotid,null as date,'NG' as classify,count(*)as Num,GROUP_CONCAT(t.id,',')as ids from (
   select max(info.id)as id, lotid,classify,max(ng.date)as date,count(*)as Num,max(ng.id)as ngid,GROUP_CONCAT(ngid,',')as ids from ng left join info on ng.id =info.ngId 
   where lotid not in(
     select lotid from ng left join info on ng.id =info.ngId where info.classify is null 
  and lotid not in(select  T.lotid from
    (select info.classify ,ng.lotid,count(*)as count from ng left join info on ng.id =info.ngId 
     group by info.classify ,ng.lotid )T group by  T.lotid having count(*)>1 )
  )
  and info.classify is not null 
   group by ng.lotid
   )T";
            list.AddRange(queryBuilder.QuerySql<StatisticsClassifyModel>(sqlNg, parameters.ToArray()));
            return list;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="list">data source</param>
        /// <returns></returns>
        private (Header, FaultTotal) GetHeadData(List<StatisticsClassifyModel> list)
        {
            Header header = new Header();

            //汇总
            var okNumber = list.Where(x => x.ClassIfy == "OK").Sum(x => x.Num);
            var ngNumber = list.Where(x => x.ClassIfy == "NG").Sum(x => x.Num);//一行
            var totalNumber = ngNumber + okNumber;
            var youLv = Math.Round(okNumber * 1m / (totalNumber == 0 ? 1 : totalNumber), 2) * 100;

            header.OKNumber = Convert.ToInt32(okNumber);
            header.NGNumber = Convert.ToInt32(ngNumber);
            header.YouxiuRate = youLv;
            header.CheckTotal = Convert.ToInt32(totalNumber);

            var elAndVi = GetDetectElAndVi();
            header.EL_NG = elAndVi[1];
            header.VI_NG = elAndVi[0];

            FaultTotal faultTotal = new FaultTotal()
            {
                EL_Fault = elAndVi[1],
                VI_Fault = elAndVi[0]
            };

            //无数据
            if (list.Count() <= 1)
            {
                header.OKNumber = 0;
                header.NGNumber = 0;
            }

            return (header, faultTotal);

        }


        private List<StatisticsClassifyModel> StatisticsDataToWeb(List<StatisticsClassifyModel> list, List<string> ClassIfyPriorityArray)
        {  
          
            Header header = new Header();
        
            //汇总
            var okNumber = list.Where(x => x.ClassIfy == "OK").Sum(x => x.Num);
            var ngNumber = list.Where(x => x.ClassIfy == "NG").Sum(x => x.Num);//一行
            var totalNumber = ngNumber+okNumber;
            var youLv = Math.Round(okNumber * 1m / (totalNumber == 0 ? 1 : totalNumber), 2) * 100;

            header.OKNumber = Convert.ToInt32(okNumber);
            header.NGNumber = Convert.ToInt32(ngNumber);
            header.YouxiuRate=youLv;
            header.CheckTotal=Convert.ToInt32(totalNumber);

            //var elAndVi = GetDetectElAndVi();
            //header.EL_NG = elAndVi[0];
            //header.VI_NG = elAndVi[1];


            //无数据
            if (list.Count() <= 1)
            {
                header.OKNumber = 0;
                header.NGNumber = 0;
            }

    

            //明细
            list.RemoveAll(x => x.ClassIfy == "OK");
            list.RemoveAll(x => x.ClassIfy == "NG");

            //总计
            var classfyGroup = list.GroupBy(x => x.ClassIfy);
            List<StatisticsClassifyModel> classifyModels = new List<StatisticsClassifyModel>();

            foreach (var itemClassfy in classfyGroup)
            {
                var classfyModel = new StatisticsClassifyModel();
                classfyModel.ClassIfy = itemClassfy.Key;
                classfyModel.DetailNum = Convert.ToInt32(itemClassfy.Sum(x => x.Num));
                // classfyModel.DetailNum = Convert.ToInt32(itemClassfy.Count());
                //classfyModel.NgIds = itemClassfy.Select(x => x.NgId).ToList();
                classifyModels.Add(classfyModel);
            }

            //sort
            List<StatisticsClassifyModel> sortClassifyModels = new List<StatisticsClassifyModel>();
            foreach (var itemClassfy in ClassIfyPriorityArray)
            {
                var item = classifyModels.Where(m => m.ClassIfy.Equals(itemClassfy)).FirstOrDefault();
                if (item != null)
                {
                    sortClassifyModels.Add(item);
                }
            }

            //source Data :classfy date(?) lotid
            //故障条码数量     
            //多个故障取优先级最高
            var lotList = list.GroupBy(x => x.LotId);

            HashSet<string> lotIds = new HashSet<string>();


            //foreach (var lotidGroup in lotList)
            //{

            //    foreach (var classfy in data.ClassIfyPriorityArray)
            //    {
            //        long classfyNumber = 0;
            //        var lotidList = list.Where(x => x.LotId == lotidGroup.Key).Where(x=>x.ClassIfy.Equals(classfy)).ToList();
            //        if (lotidList.Count() > 0)
            //        {
            //            classfyNumber++;
            //            lotIds.Add(lotidGroup.Key);
            //            //有一则跳出
            //            break;
            //        }
            //    }
            //}


            foreach (var classfy in ClassIfyPriorityArray)
            {
                long classfyNumber = 0;
                //ignore date
                foreach (var everyLotIds in list)
                {
                    if (!lotIds.Contains(everyLotIds.LotId) && everyLotIds.ClassIfy.Equals(classfy))
                    {
                        //classfyNumber= classfyNumber++
                        //classfyNumber = classfyNumber + everyLotId.Num;
                        classfyNumber++;
                        lotIds.Add(everyLotIds.LotId);
                        //下一条
                    }
                }
                var model = sortClassifyModels.Where(x => x.ClassIfy.Equals(classfy)).FirstOrDefault();
                if (model != null)
                {
                    model.Num = classfyNumber;
                }

            }

            foreach (var item in sortClassifyModels)
            {
                item.Rate = string.Format("{0:P}",
                        (double)item.Num / (ngNumber == 0 ? 1 : ngNumber));
            }

           // data.ClassIfyList = sortClassifyModels;

          

            return sortClassifyModels;

        }


        public (FaultCircle, FaultDetailBar) GetElAndViData(List<string> elClassIfyPriorityArray,List<string> viClassIfyPriorityArray)
        {
            
            //el
           var elData = GetStatisticsSourceELAlltime();
         
            var staticsModel = StatisticsDataToWeb(elData,elClassIfyPriorityArray);
           // List<ZhanBi> EL_Circle =new List<ZhanBi>();
            var el_Circle= staticsModel.Select(x=>new ZhanBi() { FaultName=x.ClassIfy, FaultNumber =Convert.ToInt32(x.Num) }).ToList();
            var el_bar = staticsModel.Select(x => new FaultDetail()
            {
                FaultName = x.ClassIfy,
                FaultNumber = Convert.ToInt32(x.Num),
                FaultRate = Convert.ToDecimal(x.Rate.Replace("%", ""))
            }).ToList();
            //vi
            var viData = GetStatisticsSourceVIAlltime();
            
            var viStaticsModel = StatisticsDataToWeb(viData, viClassIfyPriorityArray);
            var VI_Circle = viStaticsModel.Select(x => new ZhanBi() { FaultName = x.ClassIfy, FaultNumber = Convert.ToInt32(x.Num) }).ToList();
            var vi_bar = viStaticsModel.Select(x => new FaultDetail()
            {
                FaultName = x.ClassIfy,
                FaultNumber = Convert.ToInt32(x.Num),
                FaultRate = Convert.ToDecimal(x.Rate.Replace("%", ""))
            }).ToList();

            FaultCircle faultCircle = new FaultCircle()
            {
                EL_Circle = el_Circle,
                VI_Circle = VI_Circle,
            };
            FaultDetailBar faultDetailBar = new FaultDetailBar()
            {
                EL_Bar = el_bar,
                VI_Bar = vi_bar
            };
            return (faultCircle, faultDetailBar);

        }

        /// <summary>
        /// 大屏数据
        /// </summary>
        /// <param name="elClassIfyPriorityArray"></param>
        /// <param name="viClassIfyPriorityArray"></param>
        /// <returns></returns>
        //List<string> elClassIfyPriorityArray, List<string> viClassIfyPriorityArray
        public ScreenData GetScreenData()
        {
            GetXmlSetting();

            ScreenData screenData = new ScreenData();

           var dataSource = GetStatisticsSourceELAlltime();

            var headAndTotal = GetHeadData(dataSource);

            var top6 = GetTop6DetectDetail();

            var viAndelChart = GetElAndViData(elClassfiPriperty, viClassfiPriperty);

            screenData.Top6 = top6;
            screenData.Header = headAndTotal.Item1;
            screenData.FaultTotal =headAndTotal.Item2;
            screenData.FaultCircle = viAndelChart.Item1;
            screenData.FaultDetailBar = viAndelChart.Item2;


            return screenData;
        }

       
    }
}
